﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.Text;
using System.Xml;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using EQT.Model;

namespace EQT.Dal
{
    public partial class DaTestItem   //可以继承更上层接口,ITestItem 
    {

        /// <summary>
        /// 删除检验项目,多表.
        /// </summary>
        /// <param name="itemid"></param>
        /// <returns></returns>
        public bool DeleteTestItem(string itemid)
        {
            bool rc = false;
            using (DbConnection cnn = db.CreateConnection())
            {
                DbTransaction trans = null;
                try
                {
                    cnn.Open();
                    using (trans = cnn.BeginTransaction())
                    {
                        
                        //删除检验项目
                        this.Delete(itemid, trans);
                        //删除检验项目录
                        string strSQL = "DELETE FROM TEST_CATALOG  WHERE TEST_CTG_ID='" + itemid + "'";
                        db.ExecuteNonQuery(trans, CommandType.Text, strSQL);
                        //删除模板文件??????
                        
                        trans.Commit();
                    }
                    cnn.Close();
                    rc = true;
                }
                catch (DbException ex)
                {
                    if (trans != null) trans.Rollback();
                    HandleDbException(ex);
                }
            }
            return rc;
        }

        /// <summary>
        /// 创建检验项目,多表.
        /// </summary>
        /// <param name="modCatalog"></param>
        /// <param name="modItem"></param>
        /// <returns></returns>
        public bool AddTestItem(MoTestCatalog modCatalog,MoTestItem modItem)
        {
            bool rc = false;
            using (DbConnection cnn = db.CreateConnection())
            {
                DbTransaction trans = null;
                try
                {
                    cnn.Open();
                    using (trans = cnn.BeginTransaction())
                    {

                        //添加检验项目录
                        this.Add(modItem, trans);
                        //添加检验项目
                        DaTestCatalog dacatalog = new DaTestCatalog(db);
                        dacatalog.Add(modCatalog, trans);
                        trans.Commit();
                    }
                    cnn.Close();
                    rc = true;
                }
                catch (DbException ex)
                {
                    if (trans != null) trans.Rollback();
                    HandleDbException(ex);
                }
            }
            return rc;
        }

        /// <summary>
        /// 修改检验项目,多表.
        /// </summary>
        /// <param name="modCatalog"></param>
        /// <param name="modItem"></param>
        /// <returns></returns>
        public bool UpdateTestItem(MoTestCatalog modCatalog, MoTestItem modItem)
        {
            bool rc = false;
            using (DbConnection cnn = db.CreateConnection())
            {
                DbTransaction trans = null;
                try
                {
                    cnn.Open();
                    using (trans = cnn.BeginTransaction())
                    {

                        //修改检验项目录
                        this.Update(modItem, trans);
                        //修改检验项目
                        DaTestCatalog dacatalog = new DaTestCatalog(db);
                        dacatalog.Update(modCatalog, trans);
                        trans.Commit();
                    }
                    cnn.Close();
                    rc = true;
                }
                catch (DbException ex)
                {
                    if (trans != null) trans.Rollback();
                    HandleDbException(ex);
                }
            }
            return rc;
        }

        /// <summary>
        /// 查询检验项对应的doc,xls模板。
        /// </summary>
        /// <param name="ItemID"></param>
        /// <returns></returns>
        public DataSet QueryFileTemplate(string ItemID)
        {
            DataSet ds=new DataSet();
            string strSQL = "SELECT m.*, (SELECT File_Name FROM TFiles WHERE File_ID = m.Doc_Url) AS docname,"+
                            "(SELECT File_Name FROM TFiles WHERE File_ID = m.Xls_Url) AS xlsname"+
                            " FROM File_Template m LEFT OUTER JOIN"+
                            " Ref_File ref ON m.Item_Code = ref.EID LEFT OUTER JOIN"+
                            " Test_Item i ON m.Item_Code = i.Item_Code where m.item_code='"+ItemID+"'";
            try
            {
                ds = db.ExecuteDataSet(CommandType.Text, strSQL);
            }
            catch (DbException ex)
            {
                HandleDbException(ex);
            }
            return ds;
        }

        /// <summary>
        /// 删除检验项的某对模板。
        /// </summary>
        /// <param name="templateid"></param>
        /// <returns></returns>
        public bool DeleteItemTemplate(string templateid)
        {
            bool rc = false;
            using (DbConnection cnn = db.CreateConnection())
            {
                DbTransaction trans = null;
                try
                {
                    cnn.Open();
                    using (trans = cnn.BeginTransaction())
                    {
                        DaFileTemplate daTemplate = new DaFileTemplate();
                        DaRefFile daRef = new DaRefFile();
                        DaTfile daFile = new DaTfile();
                        //删除关联
                        string strDelRefFile = "delete from ref_file where eid='" + templateid + "'";
                        db.ExecuteNonQuery(trans, CommandType.Text, strDelRefFile);

                        //删除文件
                        MoFileTemplate modtemplate = daTemplate.GetEntity(templateid);
                        if (modtemplate != null)
                        {
                            daFile.Delete(modtemplate.Doc_Url,trans);
                            daFile.Delete(modtemplate.Xls_Url,trans);
                        }
                        //删除模板
                        string strDelTemplate = "delete from File_Template where FT_ID='"+templateid+"'";
                        db.ExecuteNonQuery(trans, CommandType.Text, strDelTemplate);
                        
                        
                        trans.Commit();
                    }
                    cnn.Close();
                    rc = true;
                }
                catch (DbException ex)
                {
                    if (trans != null) trans.Rollback();
                    HandleDbException(ex);
                }
            }
            return rc;
        }

        /// <summary>
        /// 添加检验项的Doc模板。
        /// </summary>
        /// <param name="motf"></param>
        /// <param name="morf"></param>
        /// <returns></returns>
        public int AddTemplateDoc(MoTfile motf, MoRefFile morf)
        {
            if (motf == null || morf == null) return 0;
            int rc = 0;
            using (DbConnection conn = db.CreateConnection())
            {
                try
                {
                    conn.Open();
                    using (DbTransaction trans = conn.BeginTransaction())
                    {
                        DaRefFile darf = new DaRefFile(db);
                        DaTfile daf = new DaTfile(db);
                        rc += daf.Add(motf, trans);
                        rc += darf.Add(morf, trans);
                        string sql = "update File_Template set doc_url='" + motf.File_Id + "' where FT_ID='" + morf.Eid + "'";
                        db.ExecuteNonQuery(trans, CommandType.Text, sql);
                        trans.Commit();
                    }
                    conn.Close();
                }
                catch (DbException de)
                {

                    throw new DalException("上传关联文件！相关实体信息：" + motf.ToString() + "\r\n" + morf.ToString(), de);
                }
            }
            return rc;
        }

        /// <summary>
        /// 添加检验项的Xls模板。
        /// </summary>
        /// <param name="motf"></param>
        /// <param name="morf"></param>
        /// <returns></returns>
        public int AddTemplateXls(MoTfile motf, MoRefFile morf)
        {
            if (motf == null || morf == null) return 0;
            int rc = 0;
            using (DbConnection conn = db.CreateConnection())
            {
                try
                {
                    conn.Open();
                    using (DbTransaction trans = conn.BeginTransaction())
                    {
                        DaRefFile darf = new DaRefFile(db);
                        DaTfile daf = new DaTfile(db);
                        rc += daf.Add(motf, trans);
                        rc += darf.Add(morf, trans);
                        string sql = "update File_Template set xls_url='" + motf.File_Id + "' where FT_ID='"+morf.Eid+"'";
                        db.ExecuteNonQuery(trans, CommandType.Text, sql);
                        trans.Commit();
                    }
                    conn.Close();
                }
                catch (DbException de)
                {

                    throw new DalException("上传关联文件！相关实体信息：" + motf.ToString() + "\r\n" + morf.ToString(), de);
                }
            }
            return rc;
        }
    }
}

